USE master 
GO

if db_id('QLRAPPHIM') IS NOT NULL
DROP DATABASE QLRAPPHIM
go

CREATE DATABASE QLRAPPHIM

GO

USE QLRAPPHIM
GO

CREATE  TABLE PHIM
(
MaPhim VARCHAR(10),
TenPhim NVARCHAR(1000),
ThongTinPhim NVARCHAR(1000),
ThongTinDienVien NVARCHAR(1000),
ThoiLuongPhim INT,
NgayBatDauChieu DATETIME,
NgayKetThucChieu DATETIME,

MaTheLoai VARCHAR(10),

PRIMARY KEY(MaPhim)
)

CREATE TABLE THELOAIPHIM
(
MaTheLoai VARCHAR(10),
TenTheLoai NVARCHAR(100),

PRIMARY KEY(MaTheLoai)
)

CREATE TABLE SUATCHIEU
(
MaSuatChieu VARCHAR(10),
ThoiGianBatDau DATETIME,

PRIMARY KEY(MaSuatChieu)
)


CREATE TABLE CTSUATCHIEU
(
MaCTSuatChieu VARCHAR(10),
ThongTinPhongChieu INT,
GiaVeNguoiLon INT,
GiaVeTreEm INT,
GiaKhuyenMai FLOAT,
SoLuongVe INT,

MaSuatChieu VARCHAR(10),
MaPhim VARCHAR(10),

PRIMARY KEY (MaCTSuatChieu)
)


CREATE TABLE KHACHHANG
(
MaKH VARCHAR(10),
HoTen NVARCHAR(100),
DiaChi NVARCHAR(100),
Email VARCHAR(50),
SoDienThoai VARCHAR(15),

PRIMARY KEY(MaKH)
)

CREATE TABLE VE
(
MaVe VARCHAR(10),
STTChoNgoi INT,
TinhTrangVe INT,
NguoiLon_TreEm BIT,

MaHD VARCHAR(10),
MaCTSuatChieu VARCHAR(10),
MaDayPC VARCHAR(2),

PRIMARY KEY ( MaVe)
)

CREATE TABLE HOADON
(
MaHD VARCHAR(10),
MaKH VARCHAR(10),
NgayLap DATETIME,
ThanhTien INT,

PRIMARY KEY(MaHD)
)

CREATE TABLE DAYGHE_PHONGCHIEU
(
MaDayPC VARCHAR(2),
SoPhong int,
DayGhe VARCHAR(2),
SoGhe INT,

PRIMARY KEY(MaDayPC)
)



GO

ALTER TABLE PHIM
ADD CONSTRAINT FK_Phim_TheLoaiPhim
FOREIGN KEY (MaTheLoai)
REFERENCES THELOAIPHIM(MaTheLoai)

ALTER TABLE CTSUATCHIEU
ADD CONSTRAINT FK_CTSuatChieu_Phim
FOREIGN KEY (MaPhim)
REFERENCES PHIM(MaPhim),

CONSTRAINT FK_CTSuatChieu_SuatChieu
FOREIGN KEY (MaSuatChieu)
REFERENCES SUATCHIEU(MaSuatChieu)

ALTER TABLE VE
ADD CONSTRAINT FK_Ve_CTSuatChieu 
FOREIGN KEY (MaCTSuatChieu)
REFERENCES CTSUATCHIEU (MaCTSuatChieu),

CONSTRAINT FK_Ve_HoaDon
FOREIGN KEY (MaHD)
REFERENCES HOADON(MaHD),

CONSTRAINT FK_Ve_DayGhePhongChieu
FOREIGN KEY (MaDayPC)
REFERENCES DAYGHE_PHONGCHIEU(MaDayPC)


ALTER TABLE HOADON 
ADD CONSTRAINT FK_HoaDon_KhachHang
FOREIGN KEY (MaKH)
REFERENCES KHACHHANG (MaKH)